with complaint_detail as ( --客诉率明细取责任网点数据
    select duty_network_code         --责任网点编码
         , duty_network_name         --责任网点名称
         , duty_network_type         --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
         , duty_type                 --责任类型
         , count(1) as complaint_cnt --客诉量
         , dt
    from jms_dm.dm_waybill_customer_complaint_detail_new_dt
    where dt >=  date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
      and dt <= '{{ execution_date | cst_ds }}'
      and is_delete = 2
      and work_type in ('普通工单', '平台工单', '理赔工单')
      and duty_network_code is not null
      and duty_network_code <> ''
    group by duty_network_code --责任网点编码
           , duty_network_name --责任网点名称
           , duty_network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
           , duty_type         --责任类型
           , dt
),
     network_massage as (
         select code          --编号
              , name          --名称
              , virt_code     --虚拟代理区code
              , virt_name     --虚拟代理区name
              , manage_code   --管理大区名称
              , manage_name   --管理大区名称
              , regional_id   --大区ID
              , regional_desc --regional_desc
              , agent_code    --代理区编码
              , agent_name    --代理区名称
              , fran_code     --加盟商编码
              , fran_name     --加盟商名称
              , center_code   --中心编码
              , center_name   --中心名称
              , provider_id   --省份ID
              , provider_desc --省份
              , city_id       --城市ID
              , city_desc     --城市
              , zone_id
              , zone_code
              , zone_name
         from jms_dim.dim_network_whole_massage
     ),
     network_arr_pick as (--网点/集散派件量和到件量
         select network_code
              , tak_cnt --收件量
              , arr_ctn --到件量
              , dt
         from jms_dm.dm_customer_complaint_network_arr_pick_summary_dt
         where dt >= date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1)
           and dt <= '{{ execution_date | cst_ds }}'
           and date_type = 1
         group by network_code
                , tak_cnt --收件量
                , arr_ctn --到件量
                , dt
     ),
     result_table as (
         select t1.duty_network_code          --责任网点编码
              , t1.duty_network_name          --责任网点名称
              , t1.duty_network_type          --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
              , t1.duty_type                  --责任类型
              , t1.complaint_cnt              --客诉量
              , t2.virt_code                  --虚拟代理区code
              , t2.virt_name                  --虚拟代理区name
              , t2.regional_id                --大区ID
              , t2.regional_desc              --regional_desc
              , t2.manage_code                --管理大区名称
              , t2.manage_name                --管理大区名称
              , t2.agent_code                 --代理区编码
              , t2.agent_name                 --代理区名称
              , t2.fran_code                  --加盟商编码
              , t2.fran_name                  --加盟商名称
              , t2.center_code                --中心编码
              , t2.center_name                --中心名称
              , t2.provider_id                --省份ID
              , t2.provider_desc              --省份
              , t2.city_id                    --城市ID
              , t2.city_desc                  --城市
              , t2.zone_code       as district_code
              , t2.zone_name       as district_desc
              , t2.zone_id         as district_id
              , nvl(t3.tak_cnt, 0) as tak_cnt --收件量
              , nvl(t3.arr_ctn, 0) as arr_ctn --到件量
              , t1.dt
         from complaint_detail t1
                  left join network_massage t2
                            on t1.duty_network_code = t2.code
                  left join network_arr_pick t3
                            on t1.duty_network_code = t3.network_code
                                and t1.dt = t3.dt
     )
insert
overwrite
table
jms_dm.dm_sqs_complaint_network_summary_dt
partition
(
dt
)
select t1.duty_network_code --责任网点编码
     , t1.duty_network_name --责任网点名称
     , t1.duty_network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
     , t1.duty_type         --责任类型
     , t1.virt_code         --虚拟代理区code
     , t1.virt_name         --虚拟代理区name
     , t1.regional_id       --大区ID
     , t1.regional_desc     --大区名称
     , t1.manage_code       --管理大区名称
     , t1.manage_name       --管理大区名称
     , t1.agent_code        --代理区编码
     , t1.agent_name        --代理区名称
     , t1.fran_code         --加盟商编码
     , t1.fran_name         --加盟商名称
     , t1.center_code       --中心编码
     , t1.center_name       --中心名称
     , t1.provider_id       --省份ID
     , t1.provider_desc     --省份
     , t1.city_id           --城市ID
     , t1.city_desc         --城市
     , t1.district_code     --片区code
     , t1.district_desc     --片区desc
     , t1.district_id       --片区id
     , t1.complaint_cnt     --客诉量
     , t1.tak_cnt           --收件量
     , t1.arr_ctn           --到件量
     , t1.dt as date_time   --日期
     , t1.dt as dt          --分区
from result_table t1
    distribute by dt